﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Mulberry.Model;
using OMG.Fill.Mulberry.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Mulberry.Service
{
    /// <summary>
    /// 表8养蚕收益调查情况T_MulSerEarnSurvey通用操作类
    /// </summary>
    public class MulSerEarnSurveyServices:MulSerEarnSurveys
    {
        DictServices dict = new DictServices();
        public Pager<List<MulSerEarnSurveyEntity>> GetPageList(MulSerEarnSurveyQuery query)
        {
            Pager<List<MulSerEarnSurveyEntity>> obj = new Pager<List<MulSerEarnSurveyEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_MulSerEarnSurvey where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<MulSerEarnSurveyEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表8养蚕收益调查情况T_MulSerEarnSurvey分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 获取指定对象
        /// </summary>
        public ResResult<List<MulSerEarnSurveyEntity>> GetList(string ReportId)
        {
            ResResult<List<MulSerEarnSurveyEntity>> obj = new ResResult<List<MulSerEarnSurveyEntity>>();
            MulSerEarnSurveyEntity model = new MulSerEarnSurveyEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = new List<MulSerEarnSurveyEntity>();
                    obj.model.Add(model);
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_MulSerEarnSurvey where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<MulSerEarnSurveyEntity>(strSql, new { ReportId = ReportId }).ToList();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取表8养蚕收益调查情况T_MulSerEarnSurvey对象信息失败", ex);

                }
            }
            return obj;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_MulSerEarnSurvey where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("判断是否生成过表1桑园面积统计表T_MulSerEarnSurvey失败", ex);
                return false;
            }
        }
        public ResResult<int> AddInfo(List<MulSerEarnSurveyEntity> list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_MulSerEarnSurvey";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "HouseName,";
            strSql += "SanType,";
            strSql += "Age,";
            strSql += "PlantSanc,";
            strSql += "Elevation,";
            strSql += "CutMethod,";
            strSql += "MulArea,";
            strSql += "SilkwormSpring,";
            strSql += "SilkwormSummer,";
            strSql += "SilkwormIsAutumn,";
            strSql += "SilkwormLateAutumn,";
            strSql += "FreshSpring,";
            strSql += "FreshSummer,";
            strSql += "FreshIsAutumn,";
            strSql += "FreshLateAutumn,";
            strSql += "PriceSpring,";
            strSql += "PriceSummer,";
            strSql += "PriceIsAutumn,";
            strSql += "PriceLateAutumn,";
            strSql += "SilkwormYield,";
            strSql += "SilkwormPrice,";
            strSql += "AverageBenefit)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@HouseName,";
            strSql += "@SanType,";
            strSql += "@Age,";
            strSql += "@PlantSanc,";
            strSql += "@Elevation,";
            strSql += "@CutMethod,";
            strSql += "@MulArea,";
            strSql += "@SilkwormSpring,";
            strSql += "@SilkwormSummer,";
            strSql += "@SilkwormIsAutumn,";
            strSql += "@SilkwormLateAutumn,";
            strSql += "@FreshSpring,";
            strSql += "@FreshSummer,";
            strSql += "@FreshIsAutumn,";
            strSql += "@FreshLateAutumn,";
            strSql += "@PriceSpring,";
            strSql += "@PriceSummer,";
            strSql += "@PriceIsAutumn,";
            strSql += "@PriceLateAutumn,";
            strSql += "@SilkwormYield,";
            strSql += "@SilkwormPrice,";
            strSql += "@AverageBenefit)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        list[i].Id = CustomFun.GetGuid();
                    }

                    string strDel = "delete from dbo.T_MulSerEarnSurvey where ReportId=@ReportId";
                    dbCon.Execute(strDel, new { ReportId = list[0].ReportId }, trans);
                    int result = dbCon.Execute(strSql, list, trans);
                    string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                    dbCon.Execute(strRep, new { ReportId = list[0].ReportId, UpdateTime = DateTime.Now }, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;

                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表4全年饲养蚕品种构成调查表T_MulSerEarnSurvey对象信息失败", ex);
                    return obj;
                }
            }
        }

        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<MulSerEarnInfoModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<MulSerEarnInfoModel> list = new List<MulSerEarnInfoModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    MulSerEarnInfoModel model = new MulSerEarnInfoModel();
                    if (row.GetCell(0) != null && row.GetCell(0).ToString() != "")
                    {
                        model.HouseName = row.GetCell(0).ToString();
                    }
                    else
                    {
                        model.HouseName = null;
                    }
                    if (row.GetCell(1) != null && row.GetCell(1).ToString() != "")
                    {
                        model.SanType = dict.GetPZKey(row.GetCell(1).ToString(), "MulLandType");
                    }
                    else
                    {
                        model.SanType = null;
                    }
                    if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                    {
                        model.Age = row.GetCell(2).ToString();
                    }
                    else
                    {
                        model.Age = null;
                    }
                    if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                    {
                        model.PlantSanc = row.GetCell(3).ToString();
                    }
                    else
                    {
                        model.PlantSanc = null;
                    }
                    if (row.GetCell(4) != null && row.GetCell(4).ToString() != "")
                    {
                        model.Elevation = decimal.Parse(row.GetCell(4).ToString());
                    }
                    else
                    {
                        model.Elevation = null;
                    }
                    if (row.GetCell(5) != null && row.GetCell(5).ToString() != "")
                    {
                        model.CutMethod = dict.GetPZKey(row.GetCell(5).ToString(), "LoggingType");
                    }
                    else
                    {
                        model.CutMethod = null;
                    }
                    if (row.GetCell(6) != null && row.GetCell(6).ToString() != "")
                    {
                        model.MulArea = decimal.Parse(row.GetCell(6).ToString());
                    }
                    else
                    {
                        model.MulArea = null;
                    }
                    if (row.GetCell(7) != null && row.GetCell(7).ToString() != "")
                    {
                        model.SilkwormSpring = decimal.Parse(row.GetCell(7).ToString());
                    }
                    else
                    {
                        model.SilkwormSpring = null;
                    }
                    if (row.GetCell(8) != null && row.GetCell(8).ToString() != "")
                    {
                        model.SilkwormSummer = decimal.Parse(row.GetCell(8).ToString());
                    }
                    else
                    {
                        model.SilkwormSummer = null;
                    }
                    if (row.GetCell(9) != null && row.GetCell(9).ToString() != "")
                    {
                        model.SilkwormIsAutumn = decimal.Parse(row.GetCell(9).ToString());
                    }
                    else
                    {
                        model.SilkwormIsAutumn = null;
                    }
                    if (row.GetCell(10) != null && row.GetCell(10).ToString() != "")
                    {
                        model.SilkwormLateAutumn = decimal.Parse(row.GetCell(10).ToString());
                    }
                    else
                    {
                        model.SilkwormLateAutumn = null;
                    }
                    if (row.GetCell(11) != null && row.GetCell(11).ToString() != "")
                    {
                        model.FreshSpring = decimal.Parse(row.GetCell(11).ToString());
                    }
                    else
                    {
                        model.FreshSpring = null;
                    }
                    if (row.GetCell(12) != null && row.GetCell(12).ToString() != "")
                    {
                        model.FreshSummer = decimal.Parse(row.GetCell(12).ToString());
                    }
                    else
                    {
                        model.FreshSummer = null;
                    }
                    if (row.GetCell(13) != null && row.GetCell(13).ToString() != "")
                    {
                        model.FreshIsAutumn = decimal.Parse(row.GetCell(13).ToString());
                    }
                    else
                    {
                        model.FreshIsAutumn = null;
                    }
                    if (row.GetCell(14) != null && row.GetCell(14).ToString() != "")
                    {
                        model.FreshLateAutumn = decimal.Parse(row.GetCell(14).ToString());
                    }
                    else
                    {
                        model.FreshLateAutumn = null;
                    }
                    if (row.GetCell(15) != null && row.GetCell(15).ToString() != "")
                    {
                        model.PriceSpring = decimal.Parse(row.GetCell(15).ToString());
                    }
                    else
                    {
                        model.PriceSpring = null;
                    }
                    if (row.GetCell(16) != null && row.GetCell(16).ToString() != "")
                    {
                        model.PriceSummer = decimal.Parse(row.GetCell(16).ToString());
                    }
                    else
                    {
                        model.PriceSummer = null;
                    }
                    if (row.GetCell(17) != null && row.GetCell(17).ToString() != "")
                    {
                        model.PriceIsAutumn = decimal.Parse(row.GetCell(17).ToString());
                    }
                    else
                    {
                        model.PriceIsAutumn = null;
                    }
                    if (row.GetCell(18) != null && row.GetCell(18).ToString() != "")
                    {
                        model.PriceLateAutumn = decimal.Parse(row.GetCell(18).ToString());
                    }
                    else
                    {
                        model.PriceLateAutumn = null;
                    }
                    if (row.GetCell(19) != null && row.GetCell(19).ToString() != "")
                    {
                        model.SilkwormYield = decimal.Parse(row.GetCell(19).ToString());
                    }
                    else
                    {
                        model.SilkwormYield = null;
                    }
                    if (row.GetCell(20) != null && row.GetCell(20).ToString() != "")
                    {
                        model.SilkwormPrice = decimal.Parse(row.GetCell(20).ToString());
                    }
                    else
                    {
                        model.SilkwormPrice = null;
                    }
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(MulSerEarnInfoModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_MulSerEarnSurvey";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "HouseName,";
            strSql += "SanType,";
            strSql += "Age,";
            strSql += "PlantSanc,";
            strSql += "Elevation,";
            strSql += "CutMethod,";
            strSql += "MulArea,";
            strSql += "SilkwormSpring,";
            strSql += "SilkwormSummer,";
            strSql += "SilkwormIsAutumn,";
            strSql += "SilkwormLateAutumn,";
            strSql += "FreshSpring,";
            strSql += "FreshSummer,";
            strSql += "FreshIsAutumn,";
            strSql += "FreshLateAutumn,";
            strSql += "PriceSpring,";
            strSql += "PriceSummer,";
            strSql += "PriceIsAutumn,";
            strSql += "PriceLateAutumn,";
            strSql += "SilkwormYield,";
            strSql += "SilkwormPrice,";
            strSql += "AverageBenefit)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@HouseName,";
            strSql += "@SanType,";
            strSql += "@Age,";
            strSql += "@PlantSanc,";
            strSql += "@Elevation,";
            strSql += "@CutMethod,";
            strSql += "@MulArea,";
            strSql += "@SilkwormSpring,";
            strSql += "@SilkwormSummer,";
            strSql += "@SilkwormIsAutumn,";
            strSql += "@SilkwormLateAutumn,";
            strSql += "@FreshSpring,";
            strSql += "@FreshSummer,";
            strSql += "@FreshIsAutumn,";
            strSql += "@FreshLateAutumn,";
            strSql += "@PriceSpring,";
            strSql += "@PriceSummer,";
            strSql += "@PriceIsAutumn,";
            strSql += "@PriceLateAutumn,";
            strSql += "@SilkwormYield,";
            strSql += "@SilkwormPrice,";
            strSql += "@AverageBenefit)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增表8养蚕收益调查情况T_MulSerEarnSurvey对象信息失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        public ResResult<int> DeleteData(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_MulSerEarnSurvey where ReportId = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除表8养蚕收益调查情况T_MulSerEarnSurvey对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 全年桑综合利用情况统计------(按年统计)
        /// </summary>
        public ResResult<List<MulSerEarnStatsModel>> GetMulSerEarnStatsList(MulNowModel model)
        {
            string ProvinceCode = "530000000000";
            DictServices dictServices = new DictServices();
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetPZKey(model.FillInYear, "ReportYear");
            }
            ResResult<List<MulSerEarnStatsModel>> result = new ResResult<List<MulSerEarnStatsModel>>();

            /*县级统计代码*/
            string StrAreaSql = "select case when Area.Code = @AreaCode then Area.Name else  isnull(area2.Name,'云南省') end ParentName ";
            StrAreaSql += ",Area.Code AreaCode, Dict.DictText FillYear, Area.FullName AreaName,Area.Coord, ";
            StrAreaSql += "ISNULL(MulArea, 0.00)   MulArea, ISNULL(SilkwormSpring, 0.00) SilkwormSpring,  ";
            StrAreaSql += "ISNULL(SilkwormSummer, 0.00) SilkwormSummer, ISNULL(SilkwormIsAutumn, 0.00) SilkwormIsAutumn,  ";
            StrAreaSql += "ISNULL(SilkwormLateAutumn, 0.00) SilkwormLateAutumn, ISNULL(FreshSpring, 0.00) FreshSpring,  ";
            StrAreaSql += "ISNULL(FreshSummer, 0.00) FreshSummer,  ISNULL(FreshIsAutumn, 0.00) FreshIsAutumn,   ";
            StrAreaSql += "ISNULL(FreshLateAutumn, 0.00) FreshLateAutumn,  ISNULL(PriceSpring, 0.00) PriceSpring,   ";
            StrAreaSql += "ISNULL(PriceSummer, 0.00) PriceSummer,  ISNULL(PriceIsAutumn, 0.00) PriceIsAutumn,   ";
            StrAreaSql += "ISNULL(PriceLateAutumn, 0.00) PriceLateAutumn,  ISNULL(SilkwormYield, 0.00) SilkwormYield,   ";
            StrAreaSql += "ISNULL(SilkwormPrice, 0.00) SilkwormPrice ";
            StrAreaSql += " from(";
            StrAreaSql += "select ";
            StrAreaSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code, ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.MulArea, 0.00))   MulArea, SUM(ISNULL(MulSerEarnSurvey.SilkwormSpring, 0.00)) SilkwormSpring,    ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.SilkwormSummer, 0.00)) SilkwormSummer, SUM(ISNULL(MulSerEarnSurvey.SilkwormIsAutumn, 0.00)) SilkwormIsAutumn,    ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.SilkwormLateAutumn, 0.00)) SilkwormLateAutumn, SUM(ISNULL(MulSerEarnSurvey.FreshSpring, 0.00)) FreshSpring,    ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.FreshSummer, 0.00)) FreshSummer,  SUM(ISNULL(MulSerEarnSurvey.FreshIsAutumn, 0.00)) FreshIsAutumn,     ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.FreshLateAutumn, 0.00)) FreshLateAutumn,  SUM(ISNULL(MulSerEarnSurvey.PriceSpring, 0.00)) PriceSpring,     ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.PriceSummer, 0.00)) PriceSummer,  SUM(ISNULL(MulSerEarnSurvey.PriceIsAutumn, 0.00)) PriceIsAutumn,     ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.PriceLateAutumn, 0.00)) PriceLateAutumn,  SUM(ISNULL(MulSerEarnSurvey.SilkwormYield, 0.00)) SilkwormYield,     ";
            StrAreaSql += "SUM(ISNULL(MulSerEarnSurvey.SilkwormPrice, 0.00)) SilkwormPrice   ";
            StrAreaSql += "from([dbo].[T_Report] Report left join T_MulSerEarnSurvey MulSerEarnSurvey on Report.Id = MulSerEarnSurvey.ReportId) ";
            StrAreaSql += "right join T_Area Area on Area.Code = Report.AreaCode ";
            StrAreaSql += "where ";
            StrAreaSql += "Area.ParentCode = @AreaCode  and Report.Status=@ReportStatus and Report.FillInYear=@FillInYear  ";
            StrAreaSql += "group by rollup(Area.Code)) tt1 left join T_Area Area on tt1.Code = Area.Code ";
            StrAreaSql += "left join T_Area  area2 on area2.Code = area.ParentCode ";
            StrAreaSql += "inner join T_Dict Dict  on Dict.GroupCode='ReportYear' and DictValue=@FillInYear ";
            StrAreaSql += "where area.ParentCode=@AreaCode  or area.Code=@AreaCode ";
            StrAreaSql += "order by AreaCode asc ";
            //统计代码
            string StrSql = "select Dict.DictText FillYear,Area.ParentCode,Area.Name AreaName,Area.Code AreaCode,Area.Coord Coord ,";
            StrSql += "MulSerEarnSurvey.HouseName HouseName, ";      //  --农户姓名
            StrSql += "MulSerEarnSurvey.Age Age,  ";       //--树龄
            StrSql += "MulSerEarnSurvey.SanType SanType,   ";     // --桑地类型(MulLandType)
            StrSql += "MulSerEarnSurvey.PlantSanc PlantSanc, ";       //  --种植规格
            StrSql += "MulSerEarnSurvey.Elevation Elevation, ";       //  --桑园海拔
            StrSql += "MulSerEarnSurvey.CutMethod CutMethod, ";     // --剪伐方式(LoggingType)
            StrSql += "ISNULL(MulSerEarnSurvey.MulArea, 0.00)   MulArea, ";    //--桑园面积
            StrSql += "ISNULL(MulSerEarnSurvey.SilkwormSpring, 0.00) SilkwormSpring, ";     // --春季养蚕量
            StrSql += "ISNULL(MulSerEarnSurvey.SilkwormSummer, 0.00) SilkwormSummer, ";      //--夏季养蚕量
            StrSql += "ISNULL(MulSerEarnSurvey.SilkwormIsAutumn, 0.00) SilkwormIsAutumn, ";      //--正秋养蚕量
            StrSql += "ISNULL(MulSerEarnSurvey.SilkwormLateAutumn, 0.00) SilkwormLateAutumn, ";    //  --晚秋养蚕量
            StrSql += "ISNULL(MulSerEarnSurvey.FreshSpring, 0.00) FreshSpring, ";      //--春季鲜茧产量
            StrSql += "ISNULL(MulSerEarnSurvey.FreshSummer, 0.00) FreshSummer,  ";    // --夏季鲜茧产量
            StrSql += "ISNULL(MulSerEarnSurvey.FreshIsAutumn, 0.00) FreshIsAutumn,  ";     //--正秋鲜茧产量
            StrSql += "ISNULL(MulSerEarnSurvey.FreshLateAutumn, 0.00) FreshLateAutumn,  ";    // --晚秋鲜茧产量
            StrSql += "ISNULL(MulSerEarnSurvey.PriceSpring, 0.00) PriceSpring,  ";    // --春季鲜茧价格
            StrSql += "ISNULL(MulSerEarnSurvey.PriceSummer, 0.00) PriceSummer,  ";     //--夏季鲜茧价格
            StrSql += "ISNULL(MulSerEarnSurvey.PriceIsAutumn, 0.00) PriceIsAutumn,  ";    // --正秋鲜茧价格
            StrSql += "ISNULL(MulSerEarnSurvey.PriceLateAutumn, 0.00) PriceLateAutumn,  ";    // --晚秋鲜茧价格
            StrSql += "ISNULL(MulSerEarnSurvey.SilkwormYield, 0.00) SilkwormYield,  ";     //--蚕沙产量
            StrSql += "ISNULL(MulSerEarnSurvey.SilkwormPrice, 0.00) SilkwormPrice  ";  //--蚕沙价格
            StrSql += "from[dbo].[T_Report] ";
            StrSql += "Report inner join T_MulSerEarnSurvey MulSerEarnSurvey on Report.Id=MulSerEarnSurvey.ReportId ";
            StrSql += "left join T_Area Area on Area.Code=Report.AreaCode ";
            StrSql += "left join T_Dict Dict on Report.FillInYear=Dict.DictValue and Dict.GroupCode='ReportYear'";
            StrSql += "where Report.FillInYear = @FillInYear and Report.Status=@ReportStatus ";
            if (model.AreaCode != ProvinceCode)//判断省级用户
            {
                StrSql += "and Area.ParentCode=@AreaCode ";
            }
            StrSql += " order by Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var paramlist = new DynamicParameters();
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    paramlist.Add("ReportStatus", model.Status);
                    List<MulSerEarnStatsModel> list = new List<MulSerEarnStatsModel>();
                    List<MulSerEarnStatsModel> listResult = new List<MulSerEarnStatsModel>();
                    List<MulSerEarnStatsModel> list2 = new List<MulSerEarnStatsModel>();
                    if(model.AreaCode != ProvinceCode)
                    {
                        listResult = dbCon.Query<MulSerEarnStatsModel>(StrAreaSql, paramlist).ToList();
                    }
                    else
                    {
                        listResult = dbCon.Query<MulSerEarnStatsModel>(StrSql, paramlist).ToList();
                    }
                   
                    
                    if (listResult != null)
                    {
                        if (model.AreaCode == DefaultKey.ProvinceCode)
                        {
                            AreaServices areaServices = new AreaServices();
                            List<AreaListModel> areaList = new List<AreaListModel>();
                            areaList = areaServices.GetAreaCodeList(model.AreaCode);
                            foreach (var item in areaList)
                            {
                                list = listResult.Where(s => s.ParentCode == item.AreaCode).ToList();
                                int count = list.Count;
                                MulSerEarnStatsModel m = new MulSerEarnStatsModel()
                                {
                                    MulArea = 0,
                                    Silkworm = 0,//养蚕量
                                    SilkwormSpring = 0,
                                    SilkwormSummer = 0,
                                    SilkwormIsAutumn = 0,
                                    SilkwormLateAutumn = 0,
                                    Fresh = 0,//鲜茧产量
                                    FreshSpring = 0,
                                    FreshSummer = 0,
                                    FreshIsAutumn = 0,
                                    FreshLateAutumn = 0,
                                    Price = 0,//鲜茧价格
                                    PriceSpring = 0,
                                    PriceSummer = 0,
                                    PriceIsAutumn = 0,
                                    PriceLateAutumn = 0,
                                    SilkwormYield = 0,//蚕沙产量和价格、平均亩桑效益、平均张种效益
                                    SilkwormPrice = 0,
                                    AverageBenefit = 0,
                                    Aver = 0
                                };
                                m.Coord = item.Coord;  m.AreaName = item.AreaName;
                                m.AreaCode = item.AreaCode;
                                if (count > 0)
                                {    
                                    foreach (var item1 in list)
                                    {
                                        m.MulArea += item1.MulArea;
                                        m.SilkwormSpring += item1.SilkwormSpring;
                                        m.SilkwormSummer += item1.SilkwormSummer;
                                        m.SilkwormIsAutumn += item1.SilkwormIsAutumn;
                                        m.SilkwormLateAutumn += item1.SilkwormLateAutumn;
                                        m.Silkworm += item1.SilkwormSpring+ item1.SilkwormSummer+ item1.SilkwormIsAutumn+ item1.SilkwormLateAutumn;//养蚕量
                                        
                                        m.FreshSpring += item1.FreshSpring;
                                        m.FreshSummer += item1.FreshSummer;
                                        m.FreshIsAutumn += item1.FreshIsAutumn;
                                        m.FreshLateAutumn += item1.FreshLateAutumn;
                                        m.Fresh += item1.FreshSpring+ item1.FreshSummer+ item1.FreshIsAutumn+ item1.FreshLateAutumn;//鲜茧产量

                                        m.PriceSpring += item1.PriceSpring/ count;
                                        m.PriceSummer += item1.PriceSummer / count;
                                        m.PriceIsAutumn += item1.PriceIsAutumn / count;
                                        m.PriceLateAutumn += item1.PriceLateAutumn / count;
                                        m.Price += (item1.PriceSpring+item1.PriceSummer+ item1.PriceIsAutumn+ item1.PriceLateAutumn) / count/4;//鲜茧价格
                                        m.SilkwormYield += item1.SilkwormYield;//蚕沙产量和价格、
                                        m.SilkwormPrice += item1.SilkwormPrice/ count;

                                        //m.AverageBenefit = 0;//平均亩桑效益
                                        //m.Aver = ()//平均张种效益
                                    }
                                }
                                list2.Add(m);
                                if(list2.Count>0)
                                {
                                    foreach(var item2 in list2)
                                    {
                                        var TotalProduct = item2.FreshSpring * item2.PriceSpring + item2.FreshSummer * item2.PriceSummer + item2.FreshIsAutumn* item2.PriceIsAutumn + item2.FreshLateAutumn* item2.PriceLateAutumn;
                                        if (item2.MulArea > 0)
                                        {
                                            item2.AverageBenefit = decimal.Round(Decimal.Parse((TotalProduct / item2.MulArea).ToString()), 1);
                                        }
                                        if(item2.Silkworm>0)
                                        {
                                            item2.Aver = decimal.Round(Decimal.Parse( (TotalProduct / item2.Silkworm).ToString()),1);
                                        }
                                    }
                                }
                            }
                        }
                        else
                        {
                            foreach (var item2 in listResult)
                            {
                                var TotalProduct = item2.FreshSpring * item2.PriceSpring + item2.FreshSummer * item2.PriceSummer + item2.FreshIsAutumn * item2.PriceIsAutumn + item2.FreshLateAutumn * item2.PriceLateAutumn;
                                if (item2.MulArea > 0)
                                {
                                    item2.AverageBenefit = decimal.Round(Decimal.Parse((TotalProduct / item2.MulArea).ToString()), 1);
                                }
                                if (item2.Silkworm > 0)
                                {
                                    item2.Aver = decimal.Round(Decimal.Parse((TotalProduct / item2.Silkworm).ToString()), 1);
                                }
                                list2.Add(item2);
                            }
                            
                        }
                        if (list2.Count > 0)
                        {
                            result.model = list2;
                            result.status = DefaultKey.SuccessResult;
                            result.message = "获取养蚕收益情况统计数据成功！";
                        }
                        else
                        {
                            result.model = null;
                            result.status = DefaultKey.InfoResult;
                            result.message = "养蚕收益情况统计数据不存在！";
                        }
                    }
                    else
                    {
                        result.status = DefaultKey.InfoResult;
                        result.message = "获取养蚕收益情况统计数据失败！";
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取养蚕收益情况统计数据失败", ex);
            }
            return result;
        }
        /// <summary>
        /// 获取时间趋势图数据列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<MulSerEarnStatsModel>> GetStatsByTimeList(MulNowModel model)
        {
            DictServices dictServices = new DictServices();
            List<Select> listDict = dictServices.GetListByGroupCode("ReportYear").model;
            ResResult<List<MulSerEarnStatsModel>> result = new ResResult<List<MulSerEarnStatsModel>>();
            List<MulSerEarnStatsModel> list = new List<MulSerEarnStatsModel>();
            if (listDict.Count > 0)
            {
                if (!string.IsNullOrEmpty(model.AreaCode))
                {
                    for (var i = 0; i < listDict.Count; i++)
                    {
                        model.FillInYear = listDict[i].label;
                        List<MulSerEarnStatsModel> listOne = GetMulSerEarnStatsList(model).model;
                        if (listOne.Count > 0)
                        {
                            list.Add(listOne[0]);
                        }
                    }
                    if (list.Count > 0)
                    {
                        result.message = "时间趋势数据列表获取成功";
                        result.model = list.OrderBy(s => s.FillYear).ToList();
                        result.status = DefaultKey.SuccessResult;
                    }
                    else
                    {
                        result.message = "时间趋势数据列表为空";
                        result.status = DefaultKey.InfoResult;
                    }
                }
                else
                {
                    result.message = "时间趋势数据列表失败,当前地区不存在";
                    result.status = DefaultKey.ErrorResult;
                }
            }
            else
            {
                result.message = "时间趋势数据列表失败,时间为空";
                result.status = DefaultKey.ErrorResult;
            }
            return result;
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(MulNowModel model)
        {
            ReportServices reportServices = new ReportServices();
            model.FillInYear = dict.GetKey(model.FillInYear);
            string ReportId = reportServices.GetReportId(model.ReportCode, model.AreaCode, model.FillInYear);
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\Excel.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulSerEarnSurvey") as HSSFSheet;//读取模板所在sheet位置
            List<MulSerEarnSurveyEntity> pagelist = GetList(ReportId).model;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(1).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(1).CellStyle = style;//对第二行第一单元格设置原来样式
            try
            {
                for (var i = 0; i < pagelist.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 3);
                    datarow.CreateCell(0).SetCellValue(pagelist[i].HouseName == null ? "" : pagelist[i].HouseName.ToString());
                    var Total1 = Common.GetDecimal(pagelist[i].SilkwormSpring) + Common.GetDecimal(pagelist[i].SilkwormSummer) + Common.GetDecimal(pagelist[i].SilkwormIsAutumn) + Common.GetDecimal(pagelist[i].SilkwormLateAutumn);
                    var Total2 = Common.GetDecimal(pagelist[i].FreshSpring) + Common.GetDecimal(pagelist[i].FreshSummer) + Common.GetDecimal(pagelist[i].FreshIsAutumn) + Common.GetDecimal(pagelist[i].FreshLateAutumn);
                    var Total3 = decimal.Zero;
                    var PINJUN1 = decimal.Zero;
                    var PINJUN2 = decimal.Zero;
                    if (Total2 != decimal.Zero)
                    {
                        Total3 = Common.GetDecimal(((pagelist[i].PriceSpring * pagelist[i].FreshSpring) + (pagelist[i].PriceSummer * pagelist[i].FreshSummer) + (pagelist[i].PriceIsAutumn * pagelist[i].FreshIsAutumn) + (pagelist[i].PriceLateAutumn * pagelist[i].FreshLateAutumn)) / Total2);

                    }
                    if (pagelist[i].MulArea != null && pagelist[i].MulArea != decimal.Zero)
                    {
                        PINJUN1 = Convert.ToDecimal(((Total2 * Total3) + (pagelist[i].SilkwormYield * pagelist[i].SilkwormPrice)) / pagelist[i].MulArea);
                    }
                    if (Total1 != decimal.Zero)
                    {
                        PINJUN2 = Convert.ToDecimal(((Total2 * Total3) + (pagelist[i].SilkwormYield * pagelist[i].SilkwormPrice)) / Total1);
                    }
                    if (pagelist[i].SanType == "1")
                    {
                        datarow.CreateCell(1).SetCellValue("√");
                    }
                    else
                    {
                        datarow.CreateCell(2).SetCellValue("√");
                    }
                    datarow.CreateCell(3).SetCellValue(pagelist[i].Age == null ? "" : pagelist[i].Age);
                    datarow.CreateCell(4).SetCellValue(pagelist[i].PlantSanc == null ? "" : pagelist[i].PlantSanc.ToString());
                    datarow.CreateCell(5).SetCellValue(pagelist[i].Elevation == null ? "" : pagelist[i].Elevation.ToString());
                    if (pagelist[i].CutMethod == "1")
                    {
                        datarow.CreateCell(6).SetCellValue("√");
                        datarow.CreateCell(7).SetCellValue("√");
                        datarow.CreateCell(8).SetCellValue("√");
                    }
                    else if (pagelist[i].CutMethod == "2")
                    {
                        datarow.CreateCell(6).SetCellValue("√");
                    }
                    else if (pagelist[i].CutMethod == "3")
                    {
                        datarow.CreateCell(7).SetCellValue("√");
                    }
                    else
                    {
                        datarow.CreateCell(8).SetCellValue("√");
                    }

                    datarow.CreateCell(9).SetCellValue(pagelist[i].MulArea == null ? "" : Common.GetOne(pagelist[i].MulArea));
                    datarow.CreateCell(10).SetCellValue(Common.GetOne(Total1));
                    datarow.CreateCell(11).SetCellValue(pagelist[i].SilkwormSpring == null ? "" : Common.GetOne(pagelist[i].SilkwormSpring));
                    datarow.CreateCell(12).SetCellValue(pagelist[i].SilkwormSummer == null ? "" : Common.GetOne(pagelist[i].SilkwormSummer));
                    datarow.CreateCell(13).SetCellValue(pagelist[i].SilkwormIsAutumn == null ? "" : Common.GetOne(pagelist[i].SilkwormIsAutumn));
                    datarow.CreateCell(14).SetCellValue(pagelist[i].SilkwormLateAutumn == null ? "" : Common.GetOne(pagelist[i].SilkwormLateAutumn));
                    datarow.CreateCell(15).SetCellValue(Common.GetOne(Total2));
                    datarow.CreateCell(16).SetCellValue(pagelist[i].FreshSpring == null ? "" : Common.GetOne(pagelist[i].FreshSpring));
                    datarow.CreateCell(17).SetCellValue(pagelist[i].FreshSummer == null ? "" : Common.GetOne(pagelist[i].FreshSummer));
                    datarow.CreateCell(18).SetCellValue(pagelist[i].FreshIsAutumn == null ? "" : Common.GetOne(pagelist[i].FreshIsAutumn));
                    datarow.CreateCell(19).SetCellValue(pagelist[i].FreshLateAutumn == null ? "" : Common.GetOne(pagelist[i].FreshLateAutumn));
                    datarow.CreateCell(20).SetCellValue(Common.GetOne(Total3));
                    datarow.CreateCell(21).SetCellValue(pagelist[i].PriceSpring == null ? "" : Common.GetOne(pagelist[i].PriceSpring));
                    datarow.CreateCell(22).SetCellValue(pagelist[i].PriceSummer == null ? "" : Common.GetOne(pagelist[i].PriceSummer));
                    datarow.CreateCell(23).SetCellValue(pagelist[i].PriceIsAutumn == null ? "" : Common.GetOne(pagelist[i].PriceIsAutumn));
                    datarow.CreateCell(24).SetCellValue(pagelist[i].PriceLateAutumn == null ? "" : Common.GetOne(pagelist[i].PriceLateAutumn));
                    datarow.CreateCell(25).SetCellValue(pagelist[i].SilkwormYield == null ? "" : Common.GetOne(pagelist[i].SilkwormYield));
                    datarow.CreateCell(26).SetCellValue(pagelist[i].SilkwormPrice == null ? "" : Common.GetOne(pagelist[i].SilkwormPrice));
                    datarow.CreateCell(27).SetCellValue(Common.GetOne(PINJUN1));
                    datarow.CreateCell(28).SetCellValue(Common.GetOne(PINJUN2));
                    //for (var j = 0; j <= 28; j++)
                    //{
                    //    datarow.GetCell(j).CellStyle = style;
                    //}
                }
                sheet.CopyTo(workbook, "Sheet1", true, true);
            }
            catch (Exception ex)
            {

            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 导出统计数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream MethodStats(List<MulSerEarnStatsModel> list)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\ExcelStats.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("MulSerEarnSurvey") as HSSFSheet;//读取模板所在sheet位置
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            var styleAreaOne = Common.GetOneAreaCellStyle(templateBook, style);
            var styleAreaInt = Common.GetIntAreaCellStyle(templateBook, style);

            #region 州市级表格样式
            var styleCityOne = Common.GetOneCityCellStyle(templateBook, style);
            var styleCityInt = Common.GetIntCityCellStyle(templateBook, style);

            #endregion
            try
            {
                if (list.Count > 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 3);
                        datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());
                        datarow.CreateCell(1).SetCellValue(list[i].HouseName == null ? "" : list[i].HouseName.ToString());
                        if (list[i].AreaName == "合计")
                        {
                            datarow.CreateCell(2).SetCellValue(list[i].SanType_T.ToString());
                            datarow.CreateCell(3).SetCellValue(list[i].SanType_D.ToString());
                        }
                        else
                        {
                            if (list[i].SanType != null && list[i].SanType != "")
                            {
                                if (list[i].SanType == "田桑")
                                {
                                    datarow.CreateCell(2).SetCellValue("✔");
                                }
                                if (list[i].SanType == "地桑")
                                {
                                    datarow.CreateCell(3).SetCellValue("✔");
                                }
                            }
                            else
                            {
                                datarow.CreateCell(2).SetCellValue("");
                                datarow.CreateCell(3).SetCellValue("");
                            }
                        }
                        datarow.CreateCell(4).SetCellValue(list[i].Age == null ? "" : list[i].Age);
                        datarow.CreateCell(5).SetCellValue(list[i].PlantSanc == null ? "" : list[i].PlantSanc.ToString());
                        datarow.CreateCell(6).SetCellValue(list[i].Elevation == null ? 0 : Convert.ToDouble(list[i].Elevation));
                        if (list[i].AreaName == "合计")
                        {
                            datarow.CreateCell(7).SetCellValue(Common.GetOne(list[i].CutMethod_C));
                            datarow.CreateCell(8).SetCellValue(Common.GetOne(list[i].CutMethod_X));
                            datarow.CreateCell(9).SetCellValue(Common.GetOne(list[i].CutMethod_ZQF));
                        }
                        else
                        {
                            if (list[i].CutMethod != null && list[i].CutMethod != "")
                            {
                                if (list[i].CutMethod == "春伐")
                                {
                                    datarow.CreateCell(7).SetCellValue("✔");
                                }
                                if (list[i].CutMethod == "夏伐")
                                {
                                    datarow.CreateCell(8).SetCellValue("✔");
                                }
                                if (list[i].CutMethod == "早秋伐")
                                {
                                    datarow.CreateCell(9).SetCellValue("✔");
                                }
                            }
                            else
                            {
                                datarow.CreateCell(7).SetCellValue("");
                                datarow.CreateCell(8).SetCellValue("");
                                datarow.CreateCell(9).SetCellValue("");
                            }
                        }
                        datarow.CreateCell(10).SetCellValue(list[i].MulArea == null ? 0.0 : Convert.ToDouble(list[i].MulArea));
                        datarow.CreateCell(11).SetCellValue(Convert.ToDouble(list[i].SilkwormSpring + list[i].SilkwormSummer + list[i].SilkwormIsAutumn + list[i].SilkwormLateAutumn));
                        datarow.CreateCell(12).SetCellValue(list[i].SilkwormSpring == null ? 0.0 : Convert.ToDouble(list[i].SilkwormSpring));
                        datarow.CreateCell(13).SetCellValue(list[i].SilkwormSummer == null ? 0.0 : Convert.ToDouble(list[i].SilkwormSummer));
                        datarow.CreateCell(14).SetCellValue(list[i].SilkwormIsAutumn == null ? 0.0 : Convert.ToDouble(list[i].SilkwormIsAutumn));
                        datarow.CreateCell(15).SetCellValue(list[i].SilkwormLateAutumn == null ? 0.0 : Convert.ToDouble(list[i].SilkwormLateAutumn));
                        datarow.CreateCell(16).SetCellValue(Convert.ToDouble(list[i].FreshSpring + list[i].FreshSummer + list[i].FreshIsAutumn + list[i].FreshLateAutumn));
                        datarow.CreateCell(17).SetCellValue(list[i].FreshSpring == null ? 0.0 : Convert.ToDouble(list[i].FreshSpring));
                        datarow.CreateCell(18).SetCellValue(list[i].FreshSummer == null ? 0.0 : Convert.ToDouble(list[i].FreshSummer));
                        datarow.CreateCell(19).SetCellValue(list[i].FreshIsAutumn == null ? 0.0 : Convert.ToDouble(list[i].FreshIsAutumn));
                        datarow.CreateCell(20).SetCellValue(list[i].FreshLateAutumn == null ? 0.0 : Convert.ToDouble(list[i].FreshLateAutumn));
                        //均价
                        datarow.CreateCell(21).SetCellValue(Convert.ToDouble(list[i].Price));
                        datarow.CreateCell(22).SetCellValue(list[i].PriceSpring == null ? 0.0 : Convert.ToDouble(list[i].PriceSpring));
                        datarow.CreateCell(23).SetCellValue(list[i].PriceSummer == null ? 0.0 : Convert.ToDouble(list[i].PriceSummer));
                        datarow.CreateCell(24).SetCellValue(list[i].PriceIsAutumn == null ? 0.0 : Convert.ToDouble(list[i].PriceIsAutumn));
                        datarow.CreateCell(25).SetCellValue(list[i].PriceLateAutumn == null ? 0.0 : Convert.ToDouble(list[i].PriceLateAutumn));
                        datarow.CreateCell(26).SetCellValue(list[i].SilkwormYield == null ? 0.0 : Convert.ToDouble(list[i].SilkwormYield));
                        datarow.CreateCell(27).SetCellValue(list[i].SilkwormPrice == null ? 0.0 : Convert.ToDouble(list[i].SilkwormPrice));
                        //平均亩桑效益（元/亩）Convert.ToDouble( MulSerEarn.Price,2)
                        datarow.CreateCell(28).SetCellValue(Convert.ToDouble(list[i].AverageBenefit));
                        //平均张种效益（元/张）
                        datarow.CreateCell(29).SetCellValue(Convert.ToDouble(list[i].Aver));
                        for (int j = 0; j < 30; j++)
                        {
                            if (datarow.GetCell(j) != null)
                            {
                                datarow.GetCell(j).CellStyle = styleAreaOne;
                                if (j == 11 || j == 12 || j == 13 || j == 14 || j == 15)
                                {
                                    datarow.GetCell(j).CellStyle = styleAreaInt;
                                }
                            }
                            else
                            {
                                datarow.CreateCell(j).CellStyle = styleAreaOne;
                                if (j == 11 || j == 12 || j == 13 || j == 14 || j == 15)
                                {
                                    datarow.GetCell(j).CellStyle = styleAreaInt;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
    }
}